package net.suncaper.psychological.mapper.impl;

import net.suncaper.psychological.mapper.CaseReportMapper;
import net.suncaper.psychological.model.domain.CaseReport;
import net.suncaper.psychological.utils.JDBCUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class CaseReportMapperImpl implements CaseReportMapper {
    
    @Override
    public int insert(CaseReport caseReport) {
        String sql = "INSERT INTO case_report (appointment_id, student_id, name, gender, department, phone, problem_type, total_sessions, self_evaluation) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
        
        try (Connection conn = JDBCUtils.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            
            pstmt.setLong(1, caseReport.getAppointmentId());
            pstmt.setLong(2, caseReport.getStudentId());
            pstmt.setString(3, caseReport.getName());
            pstmt.setString(4, caseReport.getGender());
            pstmt.setString(5, caseReport.getDepartment());
            pstmt.setString(6, caseReport.getPhone());
            pstmt.setString(7, caseReport.getProblemType());
            pstmt.setInt(8, caseReport.getTotalSessions());
            pstmt.setString(9, caseReport.getSelfEvaluation());
            
            int result = pstmt.executeUpdate();
            
            if (result > 0) {
                ResultSet rs = pstmt.getGeneratedKeys();
                if (rs.next()) {
                    caseReport.setId(rs.getLong(1));
                }
            }
            
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("插入结案报告失败", e);
        }
    }
    
    @Override
    public CaseReport findByAppointmentId(Long appointmentId) {
        String sql = "SELECT * FROM case_report WHERE appointment_id = ?";
        
        try (Connection conn = JDBCUtils.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setLong(1, appointmentId);
            ResultSet rs = pstmt.executeQuery();
            
            if (rs.next()) {
                return mapResultSetToCaseReport(rs);
            }
            
            return null;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("查询结案报告失败", e);
        }
    }
    
    @Override
    public List<CaseReport> findByStudentId(Long studentId) {
        String sql = "SELECT * FROM case_report WHERE student_id = ? ORDER BY created_at DESC";
        
        try (Connection conn = JDBCUtils.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setLong(1, studentId);
            ResultSet rs = pstmt.executeQuery();
            
            List<CaseReport> caseReports = new ArrayList<>();
            while (rs.next()) {
                caseReports.add(mapResultSetToCaseReport(rs));
            }
            
            return caseReports;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("查询学生结案报告失败", e);
        }
    }
    
    @Override
    public List<CaseReport> findByConsultantId(Long consultantId) {
        String sql = "SELECT cr.* FROM case_report cr " +
                    "JOIN appointment a ON cr.appointment_id = a.id " +
                    "JOIN duty_schedule ds ON a.duty_id = ds.id " +
                    "WHERE ds.user_id = ? ORDER BY cr.created_at DESC";
        
        try (Connection conn = JDBCUtils.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setLong(1, consultantId);
            ResultSet rs = pstmt.executeQuery();
            
            List<CaseReport> caseReports = new ArrayList<>();
            while (rs.next()) {
                caseReports.add(mapResultSetToCaseReport(rs));
            }
            
            return caseReports;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("查询咨询师结案报告失败", e);
        }
    }
    
    @Override
    public int update(CaseReport caseReport) {
        String sql = "UPDATE case_report SET name = ?, gender = ?, department = ?, phone = ?, " +
                    "problem_type = ?, total_sessions = ?, self_evaluation = ? WHERE id = ?";
        
        try (Connection conn = JDBCUtils.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setString(1, caseReport.getName());
            pstmt.setString(2, caseReport.getGender());
            pstmt.setString(3, caseReport.getDepartment());
            pstmt.setString(4, caseReport.getPhone());
            pstmt.setString(5, caseReport.getProblemType());
            pstmt.setInt(6, caseReport.getTotalSessions());
            pstmt.setString(7, caseReport.getSelfEvaluation());
            pstmt.setLong(8, caseReport.getId());
            
            return pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("更新结案报告失败", e);
        }
    }
    
    @Override
    public int deleteById(Long id) {
        String sql = "DELETE FROM case_report WHERE id = ?";
        
        try (Connection conn = JDBCUtils.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setLong(1, id);
            return pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("删除结案报告失败", e);
        }
    }
    
    @Override
    public List<CaseReport> findAll() {
        String sql = "SELECT * FROM case_report ORDER BY created_at DESC";
        
        try (Connection conn = JDBCUtils.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            ResultSet rs = pstmt.executeQuery();
            
            List<CaseReport> caseReports = new ArrayList<>();
            while (rs.next()) {
                caseReports.add(mapResultSetToCaseReport(rs));
            }
            
            return caseReports;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("查询所有结案报告失败", e);
        }
    }
    
    private CaseReport mapResultSetToCaseReport(ResultSet rs) throws SQLException {
        CaseReport caseReport = new CaseReport();
        caseReport.setId(rs.getLong("id"));
        caseReport.setAppointmentId(rs.getLong("appointment_id"));
        caseReport.setStudentId(rs.getLong("student_id"));
        caseReport.setName(rs.getString("name"));
        caseReport.setGender(rs.getString("gender"));
        caseReport.setDepartment(rs.getString("department"));
        caseReport.setPhone(rs.getString("phone"));
        caseReport.setProblemType(rs.getString("problem_type"));
        caseReport.setTotalSessions(rs.getInt("total_sessions"));
        caseReport.setSelfEvaluation(rs.getString("self_evaluation"));
        
        Timestamp createdAt = rs.getTimestamp("created_at");
        if (createdAt != null) {
            caseReport.setCreatedAt(createdAt.toLocalDateTime());
        }
        
        return caseReport;
    }
} 